package org.link.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.link.bean.Chapter;
import org.link.bean.Paper;
import org.link.bean.UserExamPaper;
import org.link.util.DBUtil;
import org.link.util.ManagerAllData;
import org.link.util.UserAllData;

/**
 * 
 * project_name:jksystem
 * package_name:org.link.dao
 * file_name:MysqlDao.java
 * @author Link
 * create_date:2015年9月19日下午1:24:46
 * modified_date:
 * description: 操作数据库类
 *
 */
public class MysqlDao {

	private DBUtil dbUtil = new DBUtil();
	
	/**
	 * 
	 * description: 查询用户名是否存在
	 * creatTime:2015年9月19日下午2:07:46
	 * @author Link
	 * return type: boolean
	 *
	 */
	public boolean queryUserExists(String name){
		Connection conn = dbUtil.getConnection();
		String sql = "select user_name from t_user where user_name = ?";
		ResultSet rs = null;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);
			rs = pstmt.executeQuery();
			while(rs.next()){
				if(rs.getString("user_name").equals(name)){
					return true;  //true代表该用户名已存在
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			dbUtil.closeResultSet(rs);
			dbUtil.closeConnection();
		}
		return false;//false代表该用户名不存在
	}
	
	/**
	 * 
	 * description: 查询用户名和密码是否正确
	 * creatTime:2015年9月19日下午1:56:57
	 * @author Link
	 * return type: boolean
	 *
	 */
	public boolean queryUserExists(String name,String password){
		Connection conn = dbUtil.getConnection();
		String sql = "select user_name,user_password from t_user where user_name = ? and user_password = ?";
		ResultSet rs = null;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setString(2, password);
			rs = pstmt.executeQuery();
			while(rs.next()){
				if(rs.getString("user_name").equals(name)&&rs.getString("user_password").equals(password)){
					return true;  //true代表用户名密码正确
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			dbUtil.closeResultSet(rs);
			dbUtil.closeConnection();
		}
		return false;//false代表用户名密码错误
	}
	/**
	 * 
	 * description: 查询管理员账号密码是否正确
	 * creatTime:2015年9月19日下午1:29:46
	 * @author Link
	 * return type: boolean
	 *
	 */
	public boolean queryManagerExists(String name,String password){
		Connection conn = dbUtil.getConnection();
		String sql = "select mana_name,mana_password from t_manager where mana_name = ? and mana_password = ?";
		ResultSet rs = null;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setString(2, password);
			rs = pstmt.executeQuery();
			while(rs.next()){
				if(rs.getString("mana_name").equals(name)&&rs.getString("mana_password").equals(password)){
					return true;  //true代表用户名密码正确
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			dbUtil.closeResultSet(rs);
			dbUtil.closeConnection();
		}
		return false;//false代表用户名密码错误
	}
	
	/**
	 * 
	 * description: 添加用户
	 * creatTime:2015年9月19日下午2:20:59
	 * @author Link
	 * return type: int
	 *
	 */
	public int addUser(String name,String password){
		int result = 1;
		Connection conn = dbUtil.getConnection();
		String sql = "insert into t_user(user_name,user_password) values (?,?)";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setString(2, password);
			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(conn!= null){
				dbUtil.closeConnection();
			}
		}
		return result;
	}
	/**
	 * 
	 * description: 读取每一章的题目信息
	 * creatTime:2015年9月19日下午5:20:02
	 * @author Link
	 * return type: void
	 *
	 */
	public void readQuestionTabel(ArrayList<Chapter> chapters){
		Connection conn = dbUtil.getConnection();
		String sql = "select ques_chapter_id,ques_image,ques_answer,ques_option_a,ques_option_b,ques_option_c,ques_option_d,ques_id,ques_tittle"
				+ " from t_question";
		ResultSet rs = null;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				int id = rs.getInt("ques_chapter_id");
				String imageAddress = rs.getString("ques_image");
				String rightAnswer = rs.getString("ques_answer");
				String quesOptionA = rs.getString("ques_option_a");
				String quesOptionB = rs.getString("ques_option_b");
				String quesOptionC = rs.getString("ques_option_c");
				String quesOptionD = rs.getString("ques_option_d");
				String quesId = rs.getString("ques_id");
				String quesTittle = rs.getString("ques_tittle");
				Chapter chapter = new Chapter(id, quesId, quesTittle, quesOptionA, quesOptionB, quesOptionC, quesOptionD, rightAnswer, imageAddress);
				chapters.add(chapter);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			dbUtil.closeResultSet(rs);
			dbUtil.closeConnection();
		}
	}
	
	/**
	 * 
	 * description: 查询试卷表中的最大编号
	 * creatTime:2015年9月20日下午12:19:07
	 * @author Link
	 * return type: int
	 *
	 */
	@SuppressWarnings("unused")
	public int queryPaperMaxId(){
		int result = 0;
		Connection conn = dbUtil.getConnection();
		String sql = "select max(paper_id) from t_paper";
		ResultSet rs = null;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				if(rs == null){
					result = 0;
				}else{
					result = rs.getInt(1);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			dbUtil.closeResultSet(rs);
			dbUtil.closeConnection();
		}
		return result;
	}
	
	/**
	 * 
	 * description: 添加试卷
	 * creatTime:2015年9月20日下午1:10:50
	 * @author Link
	 * return type: int
	 *
	 */
	public int addPaper(){
		Connection conn = dbUtil.getConnection();
		String sql = "insert into t_paper(paper_id,paper_tittle_id,paper_ques_id) values(?,?,?)";
		int result = 1;
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			for (Paper paper : ManagerAllData.papers) {
				pstmt.setInt(1, paper.getId());
				pstmt.setInt(2, paper.getTittleId());
				pstmt.setString(3, paper.getQuestionId());
				result = pstmt.executeUpdate();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			dbUtil.closeConnection();
		}
		return result;
	}
	
	/**
	 * 
	 * description: 读取试卷到内存中
	 * creatTime:2015年9月20日下午3:18:31
	 * @author Link
	 * return type: int
	 *
	 */
	public void readPaperTabel(int num,String userName){
		Connection conn = dbUtil.getConnection();
		ResultSet rs = null;
		String sql = "select ques_image,ques_answer,ques_option_a,ques_option_b,ques_option_c,ques_option_d,ques_id,ques_tittle from t_question q,"
				+ "t_paper where paper_id = ? and paper_ques_id=ques_id";//关联查询
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, num);
			rs = pstmt.executeQuery();
			while(rs.next()){
				int paperId = num;
				String tittleId = rs.getString("ques_id");
				String quesTittle = rs.getString("ques_tittle");
				String quesOptionA = rs.getString("ques_option_a");
				String quesOptionB = rs.getString("ques_option_b");
				String quesOptionC = rs.getString("ques_option_c");
				String quesOptionD = rs.getString("ques_option_d");
				String rightAnswer = rs.getString("ques_answer");
				String imageAddress = rs.getString("ques_image");
				UserExamPaper userExamPaper = new UserExamPaper(userName, paperId, tittleId, quesOptionA, quesOptionB, quesOptionC, quesOptionD, rightAnswer, imageAddress, quesTittle);
				UserAllData.userExamPapers.add(userExamPaper);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			dbUtil.closeResultSet(rs);
			dbUtil.closeConnection();
		}
	}
}













